package com.swjd.util;



import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;


//建立一个封装类
public class JDBCUtil {

	// 把连接数据库时的变量进行封装
	//private static String url = "jdbc:mysql://localhost:3306/yccz?useUnicode=true&characterEncoding=utf8";
	//private static String username = "root";
	//private static String password = "";

	static {
		try {
			// 1.注册数据库驱动
			Class.forName("com.mysql.jdbc.Driver");
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public Properties getProperty(){
		//创建一个属性对象实例
		Properties ps = new Properties();
		//根据类路径加载属性文件  
		InputStream fis = this.getClass().getResourceAsStream("/db.properties");
		try {
			ps.load(fis);
			fis.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ps;
	}
	// 封装连接方法
	public static Connection getConnections() {
		Connection conn = null;
		try {
			Properties p = new JDBCUtil().getProperty();
			String url = p.getProperty("url");
			String username = p.getProperty("username");
			String password = p.getProperty("password");
			// 2.创建连接，通过类中的封装变量进行套用
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	// 封装关闭连接方法
	public static void Close(ResultSet res, Statement state, Connection conn) {
		if (res != null) {
			try {
				res.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (state != null) {
			try {
				state.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// 增删改封装
	public static boolean execUpdate(String sql, Object[] obj) {
		boolean isScuccess = false;
		Connection conn = getConnections();
		try {
			// 添加事务
			conn.setAutoCommit(false);
			PreparedStatement ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				// 循环为sql里的?标识符赋值，对象为Object对象
				ps.setObject((i + 1), obj[i]);
			}

			int num = ps.executeUpdate();
			// 手动提交
			conn.commit();
			Close(null, ps, conn);
			if (num > 0) {
				isScuccess = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return isScuccess;
	}
	
		public static <T> List<T> queryForList(String sql, Object[] obj,Class<T> c) {
			List<T> list = new ArrayList<T>();
			Connection conn = getConnections();
			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				// 给sql语句中的？标识符设置值，用循环完成
				if(obj!=null){
					for (int i = 0; i < obj.length; i++) {
						ps.setObject((i + 1), obj[i]);
					}
				}
				ResultSet rs = ps.executeQuery();
				ResultSetMetaData rsm = rs.getMetaData();
				int colcount = rsm.getColumnCount();
				// 遍历结果集
				while (rs.next()) {
					// rm对象把每个结果转换为行(对象)
					//Object ob = rm.mapRow(rs);
					Object o = c.newInstance();//实例化
					for(int i=0;i<colcount;i++){
						Field f = c.getDeclaredField(rsm.getColumnName(i+1).toLowerCase());
						f.setAccessible(true);//打破私有作用域
						f.set(o, rs.getObject(i+1));
					}
					// 把这个值加入列表中
					list.add((T) o);
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return list;
		}
		
		public static List<Map<String,Object>> queryForList(String sql, Object[] obj) {
			List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
			Connection conn = getConnections();
			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				// 给sql语句中的？标识符设置值，用循环完成
				if(obj!=null){
					for (int i = 0; i < obj.length; i++) {
						ps.setObject((i + 1), obj[i]);
					}
				}
				ResultSet rs = ps.executeQuery();
				ResultSetMetaData rsm = rs.getMetaData();
				int colcount = rsm.getColumnCount();
				// 遍历结果集
				while (rs.next()) {
					// rm对象把每个结果转换为行(对象)
					//Object ob = rm.mapRow(rs);
					Map<String,Object> map = new HashMap<String,Object>();
					for(int i=0;i<colcount;i++){
						map.put(rsm.getColumnName(i+1).toLowerCase(), rs.getObject(i+1));
					}
					// 把这个值加入列表中
					list.add(map);
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return list;
		}
}
